﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 关系表操作类(初步审核通过)
    /// </summary>
    public class RelationDAO : IRelation
    {
        /// <summary>
        /// 向关系表中插入一行新数据, 返回执行结果受影响行数
        /// </summary>
        /// <param name="relation">要插入的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int Insert(Relation relation)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Relations]
                                    (
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    )
                                   VALUES 
                                    (
                                        @ObjectId,
                                        @ObjectType,
                                        @TermId
                                    );";
            return ExecuteSql(relation, sql);
        }

        /// <summary>
        /// 根据关系ID删除对应的关系记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除记录的ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Relations] WHERE [relation_id]=@Id;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据关系ID数组删除对应的关系记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除记录的ID数组</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids)
        {
            string ps = string.Join(",", ids);
            string sql = "DELETE FROM [&#WednTablePrefix%&_Relations] WHERE [relation_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 根据关系对象删除一个其所有关系, 返回执行结果受影响行数
        /// </summary>
        /// <param name="objectId">关系对象ID</param>
        /// <param name="objectType">关系对象类型</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int objectId, string objectType)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Relations] WHERE [object_id]=@objectId AND [object_type]=@objectType;";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@objectId", objectId),
                new SqlParameter("@objectType", objectType));
        }

        /// <summary>
        /// 更新一个完整的关系对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="relation">要更新的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateRelation(Relation relation)
        {
            string sql = @"UPDATE 
                                        [&#WednTablePrefix%&_Relations]
                                   SET
                                        [object_id]=@ObjectId,
                                        [object_type]=@ObjectType,
                                        [term_id]=@TermId
                                   WHERE
                                        [relation_id]=@Id;";
            return ExecuteSql(relation, sql);
        }

        /// <summary>
        /// 更新一个关系信息, 返回执行结果受影响行数
        /// </summary>
        /// <param name="objectId">关系对象ID</param>
        /// <param name="objectType">关系对象类型</param>
        /// <param name="oldTermId">原先的标签ID</param>
        /// <param name="newTermId">更新的标签ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateInfo(int objectId, string objectType, int oldTermId, int newTermId)
        {
            string sql = @"UPDATE 
                                        [&#WednTablePrefix%&_Relations]
                                   SET
                                        [term_id]=@newTermId
                                   WHERE
                                        [object_id]=@objectId
                                   AND
                                        [object_type]=@objectType
                                   AND
                                        [term_id]=@oldTermId;";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@objectId", objectId),
                new SqlParameter("@objectType", objectType),
                new SqlParameter("@oldTermId", oldTermId),
                new SqlParameter("@newTermId", newTermId));
        }

        /// <summary>
        /// 将一个标签ID全部更新为新的标签ID, 返回执行结果受影响行数
        /// </summary>
        /// <param name="oldTermId">原先的标签ID</param>
        /// <param name="newTermId">更新的标签ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateTermId(int oldTermId, int newTermId)
        {
            string sql = @"UPDATE 
                                        [&#WednTablePrefix%&_Relations]
                                   SET
                                        [term_id]=@newTermId
                                   WHERE
                                        [term_id]=@oldTermId;";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@oldTermId", oldTermId),
                new SqlParameter("@newTermId", newTermId));
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("relation_id", "&#WednTablePrefix%&_Relations");
        }

        /// <summary>
        /// 根据标签ID和对象类型取出当前关系表中对应的关系对象ID, 不存在则返回null
        /// </summary>
        /// <param name="objectType">关系对象类型</param>
        /// <param name="termId">标签ID</param>
        /// <returns>指定标签指定类型的全部关系对象ID数组</returns>
        public int[] SelectObjectId(string objectType, int termId)
        {
            string sql = "SELECT [object_id] FROM [&#WednTablePrefix%&_Relations] WHERE [object_Type]=@ObjectType AND [term_id]=@TermId;";
            List<int> list = new List<int>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@ObjectType", objectType), new SqlParameter("@TermId", termId)))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }
            }
            return list != null ? list.ToArray() : null;
        }

        /// <summary>
        /// 根据对象ID和对象类型取出当前关系表中对应的标签ID, 不存在则返回null
        /// </summary>
        /// <param name="objectId">关系对象ID</param>
        /// <param name="objectType">关系对象类型</param>
        /// <returns>指定对象的全部标签ID数组</returns>
        public int[] SelectTermId(int objectId, string objectType)
        {
            string sql = "SELECT [object_id] FROM [&#WednTablePrefix%&_Relations] WHERE [object_id]=@ObjectId AND [object_Type]=@ObjectType;";
            List<int> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@ObjectId", objectId), new SqlParameter("@ObjectType", objectType)))
            {
                if (reader.HasRows)
                {
                    list = new List<int>();
                    while (reader.Read())
                    {
                        list.Add(reader.GetInt32(0));
                    }
                }
            }
            return list != null ? list.ToArray() : null;
        }

        /// <summary>
        /// 取出当前关系表中的所有关系信息集合, 不存在则返回null
        /// </summary>
        /// <returns>所有关系信息集合</returns>
        public List<Relation> SelectRelationList()
        {
            string sql = @"SELECT 
                                        [relation_id],
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    FROM
                                        [&#WednTablePrefix%&_Relations];";
            return Select(sql);
        }

        /// <summary>
        /// 根据对象ID和对象类型取出当前关系表中的所有关系信息集合, 不存在则返回null
        /// </summary>
        /// <param name="objectId">关系对象ID</param>
        /// <param name="objectType">关系对象类型</param>
        /// <returns>包涵指定对象关系信息集合</returns>
        public List<Relation> SelectRelationList(int objectid, string objectType)
        {
            string sql = @"SELECT 
                                        [relation_id],
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    FROM
                                        [&#WednTablePrefix%&_Relations]
                                    WHERE
                                        [object_id]=@Id
                                    AND
                                        [object_type]=@Type;";
            return Select(sql,
                new SqlParameter("@Id", objectid),
                new SqlParameter("@Type", objectType));
        }

        /// <summary>
        /// 根据关系ID取出当前关系表中的关系信息对象, 不存在则返回null
        /// </summary>
        /// <param name="id">关系ID</param>
        /// <returns>关系信息对象</returns>
        public Relation SelectRelation(int id)
        {
            string sql = @"SELECT TOP 1
                                        [relation_id],
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    FROM
                                        [&#WednTablePrefix%&_Relations]
                                    WHERE
                                        [relation_id]=@Id;";
            List<Relation> list = Select(sql, new SqlParameter("@Id", id));
            return list != null ? list[0] : null;
        }

        #region 私有操作方法

        #region select
        /// <summary>
        /// 私有选取数据方法, 不存在则返回null
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Relation> Select(string sql, params SqlParameter[] parameter)
        {
            List<Relation> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                if (reader.HasRows)
                {
                    list = new List<Relation>();
                    while (reader.Read())
                    {
                        Relation relation = new Relation()
                        {
                            Id = reader.GetInt32(0),
                            ObjectId = reader.GetInt32(1),
                            ObjectType = reader.GetInt32(2),
                            TermId = reader.GetInt32(3)
                        };
                        list.Add(relation);
                    }
                }
            }
            return list;
        }
        #endregion

        #region ExecuteSql
        /// <summary>
        /// 私有方法执行写入数据库操作
        /// </summary>
        /// <param name="relation">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteSql(Relation relation, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@ObjectId", relation.ObjectId),
                new SqlParameter("@ObjectType", relation.ObjectType),
                new SqlParameter("@TermId", relation.TermId),
                new SqlParameter("@Id", relation.Id)
                );
        }
        #endregion

        #endregion
    }
}
